package vg.modules.filter;

import java.util.HashSet;
import java.util.List;
import java.util.Set;

import vg.services.data_base_manager.data.header.AttributeHeader;
import vg.services.data_base_manager.interfaces.GraphDataBaseFormat_1_0;
import vg.services.data_base_manager.interfaces.IGraphDataBase;
import vg.services.main_manager.MainManager;
import vg.services.user_interface_manager.additional_swing_components.conditionPanel.data.ConditionToken;

/**
 * This class works with database and so has dependency code.
 * If structure of database in project Visual Graph will changes that 
 * developers must change requests in this class.
 *
 * @author Timur Zolotuhin (e-mail: tzolotuhin@gmail.com)
 */
public class DataBaseWorker4Filter {
    public static Set<Integer> getVertexIds(List<ConditionToken> tokens, IGraphDataBase graphDataBase, int graphId) {
		if(tokens == null || graphDataBase == null)
            return null;

		// build request
        String request = "SELECT distinct v." + GraphDataBaseFormat_1_0.VERTEX_TABLE_ID +
                " FROM " + GraphDataBaseFormat_1_0.VERTEX_TABLE + " v " +
                " WHERE " + "v." + GraphDataBaseFormat_1_0.VERTEX_TABLE_GRAPH_ID + "=" + graphId;

        if(tokens.size() > 0)
            request += " and ( ";

        ConditionToken nameToken = null;
        ConditionToken valueToken = null;
        ConditionToken comparisonToken = null;
        for (ConditionToken token : tokens) {
            switch (token.getType()) {
                case ConditionToken.BRACKET_TOKEN_TYPE: {
                    request += token.getToken();
                    break;
                }

                case ConditionToken.OPERATION_TOKEN_TYPE: {
                    request += " " + token.getToken() + " ";
                    break;
                }

                case ConditionToken.STRING_ATTRIBUTE_NAME_TOKEN_TYPE:
                case ConditionToken.REAL_ATTRIBUTE_NAME_TOKEN_TYPE: {
                    nameToken = token;
                    break;
                }

                case ConditionToken.COMPARISON_TOKEN_TYPE: {
                    comparisonToken = token;
                    break;
                }

                case ConditionToken.STRING_ATTRIBUTE_VALUE_TOKEN_TYPE:
                case ConditionToken.REAL_ATTRIBUTE_VALUE_TOKEN_TYPE: {
                    valueToken = token;
                    break;
                }
            }

            if (nameToken != null && comparisonToken != null && valueToken != null && nameToken.getType() == ConditionToken.STRING_ATTRIBUTE_NAME_TOKEN_TYPE) {
                request += " v." + GraphDataBaseFormat_1_0.VERTEX_TABLE_ID + " in (" +
                        " SELECT a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_ID +
                        " FROM " + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE + " a " +
                        " WHERE " + " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_TYPE + "=" + AttributeHeader.VERTEX_OWNER_TYPE + " and " +
                                    " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_ID + "=" + "v." + GraphDataBaseFormat_1_0.VERTEX_TABLE_ID + " and " +
                                    " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_NAME + "='" + nameToken.getToken() + "' and " +
                                    " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_STRING_VALUE + " LIKE '%" + valueToken.getToken() + "%') ";
                nameToken = comparisonToken = valueToken = null;
            } else if (nameToken != null && comparisonToken != null && valueToken != null && nameToken.getType() == ConditionToken.REAL_ATTRIBUTE_NAME_TOKEN_TYPE) {
                request += " v." + GraphDataBaseFormat_1_0.VERTEX_TABLE_ID + " in (" +
                        " SELECT a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_ID +
                        " FROM " + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE + " a " +
                        " WHERE " + " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_TYPE + "=" + AttributeHeader.VERTEX_OWNER_TYPE + " and " +
                        " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_ID + "=" + "v." + GraphDataBaseFormat_1_0.VERTEX_TABLE_ID + " and " +
                        " (a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_VALUE_TYPE + "=" + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_DOUBLE_ATTRIBUTE_TYPE +
                        " or a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_VALUE_TYPE + "=" + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_INTEGER_ATTRIBUTE_TYPE + ") and " +
                        " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_NAME + "='" + nameToken.getToken() + "' and " +
                        " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_REAL_VALUE + comparisonToken.getToken() + valueToken.getToken() + ") ";
                nameToken = comparisonToken = valueToken = null;
            }
        }

        if(tokens.size() > 0)
            request += ");";
        else
            request += "; ";

		// execute request
		List<List<Object>> result = graphDataBase.executeSQLRequest(request);
		if (result != null) {
			Set<Integer> result_vertices = new HashSet<Integer>();
			for (List<Object> row : result) {
				Integer id = (Integer) row.get(0);
				result_vertices.add(id);
			}
			return (result_vertices);
		} else {
			MainManager.windowMessage.errorMessage("Can't execute SQL request.", "Filter panel error");
			return null;
		}
	}
	
	public static Set<Integer> getEdgeIds(List<ConditionToken> tokens, IGraphDataBase graphDataBase, int graphId) {
        if(tokens == null || graphDataBase == null)
            return null;

        // build request
        String request = "SELECT distinct e." + GraphDataBaseFormat_1_0.EDGE_TABLE_ID +
                " FROM " + GraphDataBaseFormat_1_0.EDGE_TABLE + " e " +
                " WHERE " + "e." + GraphDataBaseFormat_1_0.EDGE_TABLE_GRAPH_ID + "=" + graphId;

        if(tokens.size() > 0)
            request += " and ( ";

        ConditionToken nameToken = null;
        ConditionToken valueToken = null;
        ConditionToken comparisonToken = null;
        for (ConditionToken token : tokens) {
            switch (token.getType()) {
                case ConditionToken.BRACKET_TOKEN_TYPE: {
                    request += token.getToken();
                    break;
                }

                case ConditionToken.OPERATION_TOKEN_TYPE: {
                    request += " " + token.getToken() + " ";
                    break;
                }

                case ConditionToken.STRING_ATTRIBUTE_NAME_TOKEN_TYPE:
                case ConditionToken.REAL_ATTRIBUTE_NAME_TOKEN_TYPE: {
                    nameToken = token;
                    break;
                }

                case ConditionToken.COMPARISON_TOKEN_TYPE: {
                    comparisonToken = token;
                    break;
                }

                case ConditionToken.STRING_ATTRIBUTE_VALUE_TOKEN_TYPE:
                case ConditionToken.REAL_ATTRIBUTE_VALUE_TOKEN_TYPE: {
                    valueToken = token;
                    break;
                }
            }

            if (nameToken != null && comparisonToken != null && valueToken != null && nameToken.getType() == ConditionToken.STRING_ATTRIBUTE_NAME_TOKEN_TYPE) {
                request += " v." + GraphDataBaseFormat_1_0.EDGE_TABLE_ID + " in (" +
                        " SELECT a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_ID +
                        " FROM " + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE + " a " +
                        " WHERE " + " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_TYPE + "=" + AttributeHeader.EDGE_OWNER_TYPE + " and " +
                        " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_ID + "=" + "e." + GraphDataBaseFormat_1_0.EDGE_TABLE_ID + " and " +
                        " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_NAME + "='" + nameToken.getToken() + "' and " +
                        " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_STRING_VALUE + " LIKE '%" + valueToken.getToken() + "%') ";
                nameToken = comparisonToken = valueToken = null;
            } else if (nameToken != null && comparisonToken != null && valueToken != null && nameToken.getType() == ConditionToken.REAL_ATTRIBUTE_NAME_TOKEN_TYPE) {
                request += " v." + GraphDataBaseFormat_1_0.EDGE_TABLE_ID + " in (" +
                        " SELECT a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_ID +
                        " FROM " + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE + " a " +
                        " WHERE " + " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_TYPE + "=" + AttributeHeader.VERTEX_OWNER_TYPE + " and " +
                        " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_ID + "=" + "v." + GraphDataBaseFormat_1_0.VERTEX_TABLE_ID + " and " +
                        " (a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_VALUE_TYPE + "=" + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_DOUBLE_ATTRIBUTE_TYPE +
                        " or a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_VALUE_TYPE + "=" + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_INTEGER_ATTRIBUTE_TYPE + ") and " +
                        " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_NAME + "='" + nameToken.getToken() + "' and " +
                        " a." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_REAL_VALUE + comparisonToken.getToken() + valueToken.getToken() + ") ";
                nameToken = comparisonToken = valueToken = null;
            }
        }

        if(tokens.size() > 0)
            request += ");";
        else
            request += "; ";

        // execute request
		List<List<Object>> result = graphDataBase.executeSQLRequest(request);
		if (result != null) {
			Set<Integer> result_edges = new HashSet<Integer>();
			for (List<Object> row : result) {
				Integer id = (Integer) row.get(0);
				result_edges.add(id);
			}
			return (result_edges);
		} else {
			MainManager.windowMessage.errorMessage("Can't execute SQL request.", "Filter panel error");
			return null;
		}
	}
}
